﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class PhieuNhapHangDAO
    {

        public string ThemPhieuNhapHang(PhieuNhapHangDTO pnhDTO)
        {
            string mess = "";

            string sql = "Insert into";
            sql += " PHIEUNHAPHANG(MaPhieuNhap, MaNhaCungCap, NgayNhap, ThanhToan, TongTien, ConNo)";
            sql += " values(@MaPhieuNhap, @MaNhaCungCap, @NgayNhap, @ThanhToan, @TongTien, @ConNo)";
            try
            {
                SqlConnection conn = DataProvider.ConnectDB();
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlParameter para;
                para = cmd.Parameters.Add("@MaPhieuNhap", SqlDbType.Int);
                para.Value = pnhDTO.MaPhieuNhap;
                para = cmd.Parameters.Add("@MaNhaCungCap", SqlDbType.Int);
                para.Value = pnhDTO.MaNhaCungCap;
                para = cmd.Parameters.Add("@NgayNhap", SqlDbType.DateTime);
                para.Value = pnhDTO.NgayNhap;
                para = cmd.Parameters.Add("@ThanhToan", SqlDbType.Float);
                para.Value = pnhDTO.ThanhToan;
                para = cmd.Parameters.Add("@TongTien", SqlDbType.Float);
                para.Value = pnhDTO.TongTien;
                para = cmd.Parameters.Add("@ConNo", SqlDbType.Float);
                para.Value = pnhDTO.ConNo;

                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                mess = ex.Message;
            }
            return mess;
        }

        public List<PhieuNhapHangDTO> LayDanhSachPNH()
        {
            List<PhieuNhapHangDTO> ds = new List<PhieuNhapHangDTO>();
            string sql = "Select * From PHIEUNHAPHANG";

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                PhieuNhapHangDTO pnhDTO = new PhieuNhapHangDTO();
                pnhDTO.MaPhieuNhap = reader.GetInt32(0);
                pnhDTO.MaNhaCungCap = reader.GetInt32(1);
                pnhDTO.NgayNhap = reader.GetDateTime(2);
                pnhDTO.ThanhToan = reader.GetDouble(3);
                pnhDTO.TongTien = reader.GetDouble(4);
                pnhDTO.ConNo = reader.GetDouble(5);

                ds.Add(pnhDTO);
            }
            conn.Close();
            return ds;
        }

        public int LayMaPhieuNhapHang()
        {
            int max = 0;
            List<PhieuNhapHangDTO> listPNH = LayDanhSachPNH();
            if (listPNH.Count < 1)
                return max;
            for (int i = 0; i < listPNH.Count; i++)
            {
                if (listPNH[i].MaPhieuNhap > max)
                    max = listPNH[i].MaPhieuNhap;
            }
            return max;
        }

        //KHANG
        public string KetNoi()
        {
            string kq = "";
            SqlConnection conn = DataProvider.ConnectDB();
            if (conn == null)
                kq = "THat bai";
            else
                kq = "Thanh cong";
            conn.Close();
            return kq;

        }
        public List<PhieuNhapHangDTO> LayDanhSachPhieuNhapHang()
        {
            List<PhieuNhapHangDTO> danhsach = new List<PhieuNhapHangDTO>();
            string sql = "Select * From PHIEUNHAPHANG";

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                PhieuNhapHangDTO pnhDTO = new PhieuNhapHangDTO();
                pnhDTO.MaPhieuNhap = reader.GetInt32(0);
                pnhDTO.MaNhaCungCap = reader.GetInt32(1);
                pnhDTO.NgayNhap = reader.GetDateTime(2);
                pnhDTO.ThanhToan = reader.GetDouble(3);
                pnhDTO.ConNo = reader.GetDouble(4);
                pnhDTO.TongTien = reader.GetDouble(5);
                danhsach.Add(pnhDTO);
            }
            conn.Close();
            return danhsach;
        }
        public List<PhieuNhapHangDTO> Tra_cuu_phieu_nhap_hang(string maphieunhap, string tennhacungcap, DateTime tungay, DateTime denngay)
        {
            List<PhieuNhapHangDTO> danhsach = new List<PhieuNhapHangDTO>();
            string sql = "SELECT PHIEUNHAPHANG.*, NHACUNGCAP.TenNhaCungCap FROM PHIEUNHAPHANG, NHACUNGCAP WHERE PHIEUNHAPHANG.MaNhaCungCap = NHACUNGCAP.MaNhaCungCap AND NgayNhap BETWEEN '" + tungay + "' AND '" + denngay + "' ";
            if (maphieunhap != "")
                sql = sql + " AND MaPhieuNhap = '" + Convert.ToInt32(maphieunhap) + "' ";
            if (tennhacungcap != "")
                sql = sql + " AND NHACUNGCAP.TenNhaCungCap = '" + tennhacungcap + "' ";
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@maphieunhap", SqlDbType.NVarChar).Value = maphieunhap;
            cmd.Parameters.Add("@tennhacungcap", SqlDbType.NVarChar).Value = tennhacungcap;
            cmd.Parameters.Add("@tungay", SqlDbType.DateTime).Value = tungay;
            cmd.Parameters.Add("@denngay", SqlDbType.DateTime).Value = denngay;
            SqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                PhieuNhapHangDTO pnhDTO = new PhieuNhapHangDTO();
                pnhDTO.MaPhieuNhap = reader.GetInt32(0);
                pnhDTO.MaNhaCungCap = reader.GetInt32(1);
                pnhDTO.NgayNhap = reader.GetDateTime(2);
                pnhDTO.ThanhToan = reader.GetDouble(3);
                pnhDTO.ConNo = reader.GetDouble(4);
                pnhDTO.TongTien = reader.GetDouble(5);
                danhsach.Add(pnhDTO);
            }
            conn.Close();
            return danhsach;
        }

        //END KHANG
    }
}

